import json
import time
from flask import Blueprint, current_app, render_template, request
from app import db

module_nep_als = Blueprint('NEP_ALS', __name__, template_folder='templates')


@module_nep_als.route('/FoundryFinalInspection', methods=['GET'])
def foundry_final_inspection():
    # ver = request.args.get('v')
    # tpl_name = 'FoundryFinalInspection.html'
    # if ver == '1.1':
    #     tpl_name = 'FoundryFinalInspection_v1.1.html'
    # elif ver == '1.2':
    tpl_name = 'FoundryFinalInspection_v1.2.html'
    return render_template(tpl_name)


@module_nep_als.route('/api/environment_data', methods=['GET'])
def als_environment_data():
    from datetime import datetime, timedelta
    from app.helper.als import grab_lp_state, get_shift_info, get_tool_number, read_counter_from_api, \
        query_data_by_engine_kind
    # , get_prod_plan, query_als_current_output
    resp = {}
    # rtn = request.args.get('rtn')
    # rtn = rtn if rtn is not None else '8615214'
    # from_time_str = time.strftime('%d.%m.%Y 06:00:00', time.localtime(int(time.time()) - 3600 * 24))
    # to_time_str = time.strftime('%d.%m.%Y 06:00:00')
    from_time_str = time.strftime('%d.%m.%Y 06:00:00', time.localtime(time.time()))
    to_time_str = time.strftime('%d.%m.%Y %H:%M:%S', time.localtime(time.time()))
    sql = "SELECT prozessid,rtn,akz,statusid,COUNT(*) cnt FROM (SELECT dmcode,rtn,prozessid,azeit,akz,statusid," \
          "RANK() OVER (PARTITION BY dmcode,prozessid ORDER BY zeitpunkt DESC) rank FROM Als_rc.teilehist WHERE " \
          "prozessid IN (200,601) AND rtn IN('8615214', '8615218', '8616102', '8616126', '8667034', '8666898', " \
          "'8667036') AND akz IN ('LP11', 'LP12', 'LP13', 'LP14', 'LP15', 'LP16', 'LP21', 'LP22', 'LP23', 'LP24', " \
          "'LP25', 'LP26', 'LP31', 'LP32', 'LP33') AND zeitpunkt>=TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') AND " \
          "zeitpunkt<TO_DATE('%s','dd.mm.yyyy hh24:mi:ss')) GROUP BY prozessid,rtn,akz,statusid ORDER BY prozessid, " \
          "rtn" % (from_time_str, to_time_str)
    db_engine = db.get_engine(current_app, bind='ALS_NEP_Production')
    parts_data = db_engine.execute(sql)

    data = []
    tool_num_dict = {}
    akz_dict = {}
    for parts_item in parts_data:
        tmp_obj = {}
        for column, value in parts_item.items():
            tmp_obj[column] = value
        data.append(tmp_obj)
    tool_numbers = get_tool_number()
    for row in data:
        akz = row['akz']
        rtn = row['rtn']
        cnt = int(row['cnt'])
        if akz not in akz_dict:
            akz_dict[akz] = {}
            akz_dict[akz]['value'] = 0
        akz_dict[akz]['value'] += cnt
        if akz in tool_numbers:
            tool_num = rtn + '_' + tool_numbers[akz]
            if tool_num not in tool_num_dict:
                tool_num_dict[tool_num] = {}
                tool_num_dict[tool_num]['value'] = 0
            tool_num_dict[tool_num]['value'] += cnt
            tool_num_dict[tool_num]['akz'] = akz
            tool_num_dict[tool_num]['rtn'] = rtn
    # resp['counter'] = update_final_inspection_counter(tool_num_dict, akz_dict)
    resp['counter'] = read_counter_from_api()
    resp['data'] = data
    shift_info = get_shift_info(db_engine)
    shift_dict = shift_info[0]
    shift_nr = shift_info[1]
    # grid_data = get_grid_data_from_api()
    # resp['api_data'] = rebuild_final_inspection_data(grid_data, shift_dict, shift_nr)

    now_time = datetime.now()
    today_date = now_time.strftime("%Y-%m-%d")
    from_time = today_date + " 00:00:00"
    tomorrow_time = now_time + timedelta(days=1)
    tomorrow_date = tomorrow_time.strftime('%Y-%m-%d')
    to_time = tomorrow_date + " 00:00:00"
    resp['api_data'] = query_data_by_engine_kind(db_engine, from_time, to_time, 'FoundryFinalInspection', shift_info)

    resp['lp_state'] = grab_lp_state()
    resp['shift_num'] = shift_nr
    resp['tool_num'] = tool_numbers

    return json.dumps(resp)


@module_nep_als.route('/api/coreshop_data', methods=['GET'])
def coreshop_data():
    from app.helper.util import get_the_shift_range
    from app.helper.als import grab_coreshop_state, read_counter_from_api, rebuild_rtns, \
        get_coreshop_info_by_motor_type, get_shift_info

    # akz_list = ('CS11', 'CS12', 'CS13', 'CS14',
    #             'CS21', 'CS22', 'CS23', 'CS24', 'CS25',
    #             'CS41', 'CS42', 'CS43', 'CS44', 'CS45')

    point_data_dict = {}

    srl = ({'from': '06:00', 'to': '14:00'},
           {'from': '14:00', 'to': '22:00'},
           {'from': '22:00', 'to': '06:00'})
    srd = get_the_shift_range(srl)
    shift_start_time = srd.get('from')
    # shift_end_time = srd.get('to')
    to_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

    sql = "SELECT AKZ, COUNT(*) CNT FROM KERNSCHUSSDATEN WHERE AKZ LIKE 'CS%' AND " \
          "ZEITPUNKT >= TO_DATE('" + shift_start_time + "', 'yyyy-mm-dd hh24:mi:ss') AND " \
                                                        "ZEITPUNKT  < TO_DATE('" + to_time_str + "', 'yyyy-mm-dd hh24:mi:ss') GROUP BY AKZ"

    db_engine = db.get_engine(current_app, bind='ALS_NEP_Production')
    rows = db_engine.execute(sql)
    for row in rows:
        akz = row['akz']
        current_val = row['cnt']
        point_data_dict[akz] = {'current': current_val}

    # passed_second = compute_the_time_difference(shift_start_time, to_time_str)
    # total_second = compute_the_time_difference(shift_start_time, shift_end_time)

    sql = "SELECT ANLAGEN,SOLLSTUECK FROM ANDOONGRUPPEN WHERE ANLAGEN LIKE 'CS%'"
    rows = db_engine.execute(sql)
    for row in rows:
        akz = row['anlagen']
        target_val = row['sollstueck']
        item = point_data_dict.get(akz, {})
        item['target'] = target_val
        # item['planned'] = calc_planned_num(target_val, passed_second, total_second)
        point_data_dict[akz] = item

    sql = "SELECT ANLAGEN_KZ,WERKZEUGE from anlagenstatus where anlagen_kz like 'CS%' AND ANLAGEN_KZ NOT LIKE'CSE%'"
    rows = db_engine.execute(sql)
    for row in rows:
        akz = row['anlagen_kz']
        tool_info = row['werkzeuge']
        tool_vals = tool_info.split(',')
        rtns = tool_vals[18]
        tool_number = tool_vals[19]
        item = point_data_dict.get(akz, {})
        item['rtns'] = rebuild_rtns(rtns)
        item['tool_number'] = tool_number
        point_data_dict[akz] = item

    api_data = grab_coreshop_state()

    for akz, api_item in api_data.items():
        item = point_data_dict.get(akz, {})
        point_data_dict[akz] = dict(item, **api_item)
    shift_info_list = get_shift_info(db_engine)
    shift_dict = shift_info_list[0]
    shift_num = shift_info_list[1]
    api_data = get_coreshop_info_by_motor_type(db_engine, shift_dict, shift_num)
    resp = {'data': point_data_dict, 'counter': read_counter_from_api("coreshop"), 'api_data': api_data,
            'shift_num': shift_num}
    return json.dumps(resp)


# 从数据库中获取生产数据
@module_nep_als.route('/api/engine_data', methods=['GET'])
def engine_data():
    from datetime import datetime, timedelta
    from app.helper.als import get_shift_info, query_data_by_engine_kind
    from app.helper.util import is_date

    db_eng = db.get_engine(current_app, bind='ALS_NEP_Production')
    from_date = request.args.get('from_date')
    engine_kind = request.args.get('type')
    engine_kind = engine_kind if engine_kind is not None else 'Final'

    shift_info = get_shift_info(db_eng)
    shift_dict = shift_info[0]  # 班次时间字典
    # current_shift_num = shift_info[1]  # 当前时间所处的班次
    # now_time = datetime.now()
    production_date = shift_info[2]
    system_date = time.strftime('%Y-%m-%d')

    if is_date(from_date) and system_date != from_date:
        from_time = from_date + " 00:00:00"
        from_datetime = datetime.strptime(from_date, "%Y-%m-%d")
        to_datetime = from_datetime + timedelta(days=1)
        to_date = to_datetime.strftime('%Y-%m-%d')
        to_time = to_date + " 00:00:00"
    else:
        from_time = production_date + " 00:00:00"
        from_datetime = datetime.strptime(production_date, "%Y-%m-%d")
        to_datetime = from_datetime + timedelta(days=1)
        to_date = to_datetime.strftime('%Y-%m-%d')
        to_time = to_date + " 00:00:00"
    # print('!!!!!!!')
    # print(from_time)
    # print(to_time)
    # print(shift_info)
    resp_data = query_data_by_engine_kind(db_eng, from_time, to_time, engine_kind, shift_info)
    resp = {'data': resp_data, 'shift_info': shift_dict}
    return json.dumps(resp)


@module_nep_als.route('/FoundryRTB2', methods=['GET'])
def foundry_rtb2():
    tpl_name = 'FoundryRTB2.html'
    return render_template(tpl_name)


@module_nep_als.route('/FoundryCoreshop', methods=['GET'])
def foundry_coreshop():
    tpl_name = 'FoundryCoreshop.html'
    return render_template(tpl_name)


@module_nep_als.route('/api/rtb2', methods=['GET'])
def als_rtb2_data():
    from datetime import datetime, timedelta
    from app.helper.als import get_shift_info, grab_rm_state, query_data_by_engine_kind
    resp = {}
    from_time_str = time.strftime('%d.%m.%Y 06:00:00', time.localtime(time.time()))
    to_time_str = time.strftime('%d.%m.%Y %H:%M:%S', time.localtime(time.time()))
    sql = "SELECT prozessid,akz,statusid,COUNT(*) cnt FROM (SELECT dmcode,prozessid,akz,statusid,RANK() OVER " \
          "(PARTITION BY dmcode,prozessid ORDER BY prozessid,zeitpunkt DESC) rang FROM Als_rc.teilehist WHERE " \
          "zeitpunkt>=TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') AND zeitpunkt<TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') " \
          "AND prozessid<900 AND SUBSTR(dmcode,1,1)='2' AND akz IN ('LP11','LP12','LP13','LP14','LP15','LP16'," \
          "'LP21','LP22','LP23','LP24','LP25','LP26','LP31','LP32','LP33','LE01','LE02','CR01','CR02','MDEC','SA01'," \
          "'SA02','MSAW','XR12','XR21','XR22','HRO1','CT10','CT09','HT02','MWBH','HNT1','MHC1','CT11','RM10','RM20'," \
          "'RM30','RM40','RM50','RM60','MP10','RM11','RM21','RM22','RM31','RM41','RM42','RM51','RM61','RM62','MP20'," \
          "'CM01','CM02','CM03','LT01','LT02','LT03','LT04','MLT1','HD01','HD02','HD03','HHDS','CI01','CI02','CI03'," \
          "'LD10','LD20','LD30','LD40','LD50','LD60','MLDS','CT41','CT42','CT43','CT44','CT68','CT69','MAMQ', " \
          "'WLS1','WLS2','WLS3') AND rtn IN ('8615214','8615218','8616102','8616126','8667034','8666898','8667036') " \
          "AND azeit IS NOT NULL) WHERE rang=1 GROUP BY prozessid,akz,statusid " \
          "ORDER BY prozessid,akz,statusid" % (from_time_str, to_time_str)

    # print('##########')
    # print(sql)
    # print('##########')
    db_engine = db.get_engine(current_app, bind='ALS_NEP_Production')
    parts_data = db_engine.execute(sql)

    akz_dict = {}
    for parts_item in parts_data:
        if parts_item['akz'] not in akz_dict:
            akz_dict[parts_item['akz']] = {}
        if parts_item['statusid'] == 1:
            akz_dict[parts_item['akz']]['ok'] = parts_item['cnt']
        else:
            akz_dict[parts_item['akz']]['nok'] = parts_item['cnt']
    # api_data = get_grid_data_from_api()
    resp['akz_dict'] = akz_dict
    # resp['rtb2_data'] = rebuild_rtb2_data(api_data)

    shift_info = get_shift_info(db_engine)
    shift_dict = shift_info[0]  # 班次时间字典

    now_time = datetime.now()
    today_date = now_time.strftime("%Y-%m-%d")
    from_time = today_date + " 00:00:00"
    tomorrow_time = now_time + timedelta(days=1)
    tomorrow_date = tomorrow_time.strftime('%Y-%m-%d')
    to_time = tomorrow_date + " 00:00:00"
    resp['rtb2_data'] = query_data_by_engine_kind(db_engine, from_time, to_time, 'RTB2', shift_info)
    resp['akz_state'] = grab_rm_state()
    return json.dumps(resp)


@module_nep_als.route('/test')
def test():
    from app.helper.als import get_shift_info
    db_engine = db.get_engine(current_app, bind='ALS_NEP_Production')
    shift_info = get_shift_info(db_engine)
    return 'hello'


@module_nep_als.route('/FoundryDailyProduction', methods=['GET'])
def foundry_daily_production():
    tpl_name = 'FoundryDailyProduction.html'
    return render_template(tpl_name)


@module_nep_als.route('/CoreShop', methods=['GET'])
def core_shop():
    tpl_name = 'CoreShop.html'
    return render_template(tpl_name)


@module_nep_als.route('/api/FoundryDailyProduction', methods=['GET'])
def api_for_foundry_daily_production():
    from app.helper.foundry_daily_production import build_teilehis_data
    db_engine = db.get_engine(current_app, bind='ALS_NEP_Production')

    date_from_str = request.args.get('date_from')
    date_to_str = request.args.get('date_to')

    if not (date_from_str and date_to_str):
        date_to_str = time.strftime('%d.%m.%Y')
        date_to_tuple = time.strptime(date_to_str, '%d.%m.%Y')
        date_to_stamp = time.mktime(date_to_tuple)
        date_from_stamp = date_to_stamp - 24 * 3600
        date_from_tuple = time.localtime(date_from_stamp)
        date_from_str = time.strftime('%d.%m.%Y', date_from_tuple)

    from_time_str = date_from_str + ' 06:00:00'
    to_time_str = date_to_str + ' 06:00:00'

    actual_volume = build_teilehis_data(db_engine, from_time_str, to_time_str)
    resp = {'actualVolume': actual_volume}
    return json.dumps(resp)


@module_nep_als.route('/api/SetFoundryDailyProductionData', methods=['GET', 'POST'])
def api_set_foundry_daily_production_data():
    result = '1'
    if request.method == 'POST':
        result = '0'
        cache_data = request.form['data']
        fo = open("./cache/FoundryDailyProductionData", "w")
        fo.write(cache_data)
        fo.close()
    return result


@module_nep_als.route('/api/GetFoundryDailyProductionData')
def api_get_foundry_daily_production_data():
    import os
    file_path = "./cache/FoundryDailyProductionData"
    result = '1'
    if os.path.isfile(file_path):
        fo = open(file_path, "r")
        result = fo.read()
        fo.close()
        if not result:
            result = '0'
    return result


@module_nep_als.route('/api/Assembly', methods=['GET'])
def api_for_assembly():
    from app.helper.assembly import get_assembly_production
    from app.helper.assembly import get_hvb_production
    db_eng_1 = db.get_engine(current_app, bind='IPS_T_NEP_Prod_Archive')
    db_eng_2 = db.get_engine(current_app, bind='IPS_T_NEP_Prod_Online')

    shift_dict = {
        'shift_1_start_time': '06:00:00',
        'shift_1_end_time': '14:00:00',
        'shift_2_start_time': '14:00:00',
        'shift_2_end_time': '22:00:00',
        'hvb_shift_1_start_time': '06:00:00',
        'hvb_shift_1_end_time': '15:00:00',
        'hvb_shift_2_start_time': '15:00:00',
        'hvb_shift_2_end_time': '00:00:00',
        'shift_total_second': 3600 * 7.75
    }

    assembly_production = get_assembly_production(db_eng_1, db_eng_2, shift_dict)
    hvb_production = get_hvb_production(db_eng_1, db_eng_2, shift_dict)
    resp = {'assembly_shifts_info': assembly_production, 'hvb_shifts_info': hvb_production, 'shift_dict': shift_dict}
    return json.dumps(resp)


@module_nep_als.route('/api/HeatTreatment', methods=['GET'])
def api_for_heat_treatment():
    from app.helper.als import get_heat_treatment_volume
    db_eng = db.get_engine(current_app, bind='ALS_NEP_Production')
    resp = get_heat_treatment_volume(db_eng)
    return json.dumps(resp)


@module_nep_als.route('/HeatTreatmentOverview')
def heat_treatment_overview():
    tpl_name = 'NEPHeatTreatmentOverview.html'
    return render_template(tpl_name)


@module_nep_als.route('/api/ResetFoundryFinalInspectionCounter', methods=['POST'])
def reset_foundry_final_inspection_counter():
    import socket
    hostname = socket.gethostname()
    tool_number = request.form['tool_number']
    rtn = request.form['rtn']
    resp = {
        'status': 0
    }
    if hostname == 'lixtix0osmapi01':
        resp = _reset_foundry_final_inspection_counter(rtn, tool_number)
    else:
        import urllib.request
        import urllib.parse
        post_body = {'rtn': rtn, 'tool_number': tool_number, 'time': time.time()}
        post_data = urllib.parse.urlencode(post_body).encode('utf-8')
        url = 'http://lixtix0osmapi01/NEP/ALS/api/ResetFoundryFinalInspectionCounter'
        req = urllib.request.Request(url, post_data)
        rh = urllib.request.urlopen(req)
        content = rh.read().decode('utf-8')
        rh.close()
        if content and content.find('{') == 0:
            resp = content
            # print('####################')
            # print(resp)
    return resp


def _reset_foundry_final_inspection_counter(rtn, tool_number):
    from app.helper.mysql import link_mysql
    resp = {
        'status': 0
    }
    conn = link_mysql()
    try:
        with conn.cursor() as cursor:
            sql = 'UPDATE `tool_number_counter` SET flag=1 WHERE rtn="%s" AND tool_number="%s"' % (rtn, tool_number)
            # print('^^^^^^^^^^^^^^')
            # print(sql)
            cursor.execute(sql)
            conn.commit()
            resp['status'] = 1
    finally:
        conn.close()
    return json.dumps(resp)


@module_nep_als.route('/api/HVB', methods=['GET'])
def api_for_hvb():
    from app.helper.assembly import query_hvb_values
    db_engine = db.get_engine(current_app, bind='IPS_T_NEP_Prod_Online')
    shifts_info = query_hvb_values(db_engine)
    resp = {'shifts_info': shifts_info}
    return json.dumps(resp)


@module_nep_als.route('/api/ResetFoundryCoreshopCounter', methods=['POST'])
def reset_foundry_coreshop_counter():
    import socket
    hostname = socket.gethostname()
    tool_number = request.form['tool_number']
    rtns = request.form['rtns']
    resp = {
        'status': 0
    }
    if hostname == 'lixtix0osmapi01':
        resp = _reset_foundry_coreshop_counter(rtns, tool_number)
    else:
        import urllib.request
        import urllib.parse
        post_body = {'rtns': rtns, 'tool_number': tool_number, 'time': time.time()}
        post_data = urllib.parse.urlencode(post_body).encode('utf-8')
        url = 'http://lixtix0osmapi01/NEP/ALS/api/ResetFoundryCoreshopCounter'
        req = urllib.request.Request(url, post_data)
        rh = urllib.request.urlopen(req)
        content = rh.read().decode('utf-8')
        rh.close()
        if content and content.find('{') == 0:
            resp = content
    return resp


def _reset_foundry_coreshop_counter(rtns, tool_number):
    from app.helper.mysql import link_mysql
    resp = {
        'status': 0
    }
    conn = link_mysql()
    try:
        with conn.cursor() as cursor:
            sql = 'UPDATE `coreshop_tool_number_counter` SET flag=1 WHERE rtns="%s" AND ' \
                  'tool_number="%s"' % (rtns, tool_number)
            # print("###########")
            # print(sql)
            cursor.execute(sql)
            conn.commit()
            resp['status'] = 1
    finally:
        conn.close()
    return json.dumps(resp)
